﻿DECLARE @alter NVARCHAR(MAX)
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_medianamik_UrlXmlDoc]'))
BEGIN
	SELECT @alter = '
ALTER VIEW [dbo].[vw_medianamik_UrlXmlDoc]
AS
WITH Node AS (SELECT     npv1.NodeId, n.TypeId, npv1.CultureName, npv1.Revision, CAST(npv1.Value AS xml) AS Xml, 
                                                         CASE WHEN npv1.Revision IS NULL THEN - 1 ELSE COUNT(npv2.Revision) - 1 END AS Generation, ni.IsActive
                                  FROM         dbo.medianamik_NodePropertyValue AS npv1 INNER JOIN
                                                         dbo.medianamik_Node AS n ON n.NodeId = npv1.NodeId INNER JOIN
                                                         dbo.medianamik_Property AS p ON p.PropertyId = npv1.PropertyId INNER JOIN
                                                         dbo.medianamik_NodeInstance AS ni ON ni.NodeId = npv1.NodeId AND ni.CultureName = npv1.CultureName LEFT OUTER JOIN
                                                         dbo.medianamik_NodePropertyValue AS npv2 ON p.PropertyId = npv2.PropertyId AND npv1.NodeId = npv2.NodeId AND 
                                                         npv1.CultureName = npv2.CultureName
                                  WHERE     (p.Name = ''url'') AND (n.IsDeleted = 0) AND (ni.IsActive = 1) AND (ISNULL(npv2.Revision, dbo.fn_medianamik_MaxRevision()) >= ISNULL(npv1.Revision, 
                                                         dbo.fn_medianamik_MaxRevision()))
                                  GROUP BY npv1.NodeId, n.TypeId, npv1.CultureName, npv1.Revision, npv1.Value, ni.IsActive)
    SELECT     NodeId, TypeId, CultureName, Revision, Xml, Generation
     FROM         Node AS Node_1
     WHERE     (Generation <= 0) AND (IsActive = 1)'
     	EXEC sp_executesql @alter
END